<?php
App::uses('AppController', 'Controller');
/**
 * Users Controller
 *
 * @property User $User
 */
class ReportsController  extends AppController {
	
	public function beforeFilter() {
            $this->layout = "backend"; 
            parent::beforeFilter();
    					
	}

/**
 * index method
 *
 * @return void
 */
	public function index() {					
            $this->loadModel('User');        
             $sql = "select "
                    . "count(qa.ruler) as ruler,sum(qa.total_time) as total_time, me.name,me.code,me.location "
                    . "from question_answers as qa "
                    . "INNER JOIN  members as me "
                    . "ON qa.name_member = me.name and qa.munber = me.location " 
                    . "where qa.ruler = 1 "
                    . "group by qa.munber,qa.name_member order by count(qa.ruler) DESC , sum(total_time) ASC " ;  
          
           
            $datas = $this->User->query($sql);
            $location=$this->Common->getLocation();            
             $dataFull = array();
			foreach ($datas as $data):
                $dataFull[]= array(
                    'countTrue'=>$data[0]['ruler'],
                    'countTime'=>$data[0]['total_time'],
                    'name'=>$data['me']['name'],  
                    'code'=>$data['me']['code'],
                    'location'=>$location[$data['me']['location']],
                );
            endforeach;
			error_reporting(E_ERROR | E_WARNING | E_PARSE);
            require 'PHPExcel/PHPExcel.php';
			require 'PHPExcel/PHPExcel/Writer/Excel5.php';
			$path_xls=$_SERVER['DOCUMENT_ROOT']."/app/webroot/files/";
			$objExcel = new PHPExcel();
			$objWriter = new PHPExcel_Writer_Excel5($objExcel);
			$objActSheet = $objExcel->getActiveSheet();
			$default_border = array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
				'color' => array('rgb'=>'FFFFFF')
			);
		
			$style_header = array(
				'borders' => array(
					'bottom' => $default_border,
					'left' => $default_border,
					'top' => $default_border,
					'right' => $default_border,
				),
				'fill' => array(
					'type' => PHPExcel_Style_Fill::FILL_SOLID,
					'color' => array('rgb'=>'70AED3'),
				),
				'font' => array(
					'bold' => true,
					'color' => array('rgb'=>'ffffff')
				)
			);
			
			
			
			$objActSheet->setCellValue("A1", "EPD Mid-Year Meeting 2014 Pharmacovigillance Refresh-Training ");
			
			$objActSheet->mergeCells("A1:F1");	
			$objActSheet->getStyle("A1:F1")->applyFromArray(array("font" => array( "bold" => true)));			
			
			$objActSheet->setCellValue("A2","STT");
			$objActSheet->setCellValue("B2","Tên nhân viên");
			$objActSheet->setCellValue("C2","Mã số nhân viên");
			$objActSheet->setCellValue("D2","Phòng ban");
			$objActSheet->setCellValue("E2","Số câu trả lời đúng");
			$objActSheet->setCellValue("F2","Tổng số thời gian trả lời");
			
			$objActSheet->getStyle("A2:F2")->applyFromArray(array("font" => array( "bold" => true)));
			 $i=1;
			 $row = 3;
            foreach ($dataFull as $value):
				if($value['countTrue'] > 5){
				   $objActSheet->setCellValue("A".$row,$i);
				   $objActSheet->setCellValue("B".$row,$value['name']);
				   $objActSheet->setCellValue("C".$row,$value['code']);
				   $objActSheet->setCellValue("D".$row,$value['location']);
				   $objActSheet->setCellValue("E".$row,$value['countTrue']);
				   $objActSheet->setCellValue("F".$row,$value['countTime']);
			   }
                $i++;
				$row ++;
            endforeach;
			$objWriter->save($path_xls."export.xls");			
            $this->set('fullData',$dataFull);
            $this->render('/Backend/Reports/index');
	}
        public function export(){			
            error_reporting(E_ERROR | E_WARNING | E_PARSE);
             require 'PHPExcel/PHPExcel.php';
			 require 'PHPExcel/PHPExcel/Writer/Excel5.php';
			$path_xls="//usr/project/apache_eth0/http_80/abbott_htdocs/app/webroot/files/";
			$objExcel = new PHPExcel();
			$objWriter = new PHPExcel_Writer_Excel5($objExcel);
			$objActSheet = $objExcel->getActiveSheet();
			$objActSheet->setCellValue("A1", "EPD Mid-Year Meeting 2014 Pharmacovigillance Refresh-Training ");
			$objActSheet->mergeCells("A1:F1");
			
			$objActSheet->setCellValue("A2","STT");
			$objActSheet->setCellValue("B2","TÃªn nhÃ¢n viÃªn");
			$objActSheet->setCellValue("C2","MÃ£ sá»‘ nhÃ¢n viÃªn");
			$objActSheet->setCellValue("D2","PhÃ²ng ban");
			//$objActSheet->setCellValue("E2","Sá»‘ cÃ¢u tráº£ lá»�i Ä‘Ãºng");
			//$objActSheet->setCellValue("F2","Tá»•ng thá»�i gian tráº£ lá»�i");
			$this->loadModel('User');        
            $sql = "select "
                    . " * "
                    . "from members "  ;  
            
            $datas = $this->User->query($sql);
			
			pr($datas);die;
            $location=$this->Common->getLocation(); 
			$dataFull = array();            
			foreach ($datas as $data):
                $dataFull[]= array(
                    'countTrue'=>$data[0]['ruler'],
                    'countTime'=>$data[0]['total_time'],
                    'name'=>$data['me']['name'],  
                    'code'=>$data['me']['code'],
                    'location'=>$location[$data['me']['location']],
                );
            endforeach; 
			 $i=1;
			 $row = 3;
            foreach ($dataFull as $value):
               $objActSheet->setCellValue("A".$row,$i);
               $objActSheet->setCellValue("B".$row,$value['name']);
			   $objActSheet->setCellValue("C".$row,$value['code']);
			   $objActSheet->setCellValue("D".$row,$value['location']);
			   $objActSheet->setCellValue("E".$row,$value['countTrue']);
			   $objActSheet->setCellValue("F".$row,$value['countTime']);
                $i++;
            endforeach;
			$objWriter->save($path_xls."TEST.xls");
			$file = $path_xls.'TEST.xls';
			$download_name = basename($file);
			if (file_exists($file)) {
				header('Content-Type: application/octet-stream');
				header('Content-Disposition: attachment; filename='.$download_name);
				header('X-Sendfile: '.$file);
				exit;
			}
		    
            
            $this->render('/Backend/Reports/export');
        }

}
